package com.moter.one.wyydemo1.mapper;
import com.moter.one.wyydemo1.entity.SongInfo;
import com.moter.one.wyydemo1.entity.UserInfo;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.Date;
import java.util.List;

//定义数据访问层接口，定义MyBatis数据操作接口
@Mapper
public interface SongInfoMapper {
    //插入一搜音乐的所有信息
    @Insert("insert into songstable(song_id,song_name,song_describing,picture_address," +
            "music_address,video_address,singer_name,date,lyrics,like_number,amount_of_play,type,collected)" +
            " values(#{song_id},#{song_name},#{song_describing},#{picture_address}," +
            "#{music_address},#{video_address},#{singer_name},#{date},#{lyrics},#{like_number},#{amount_of_play},#{type},#{collected})")
    int insertSongInfo(SongInfo songInfo);


    //根据类型查询播放量前8的歌曲userdb@123.57.90.42
    @Select("select * " +
            "from songstable " +
            "where type = #{type} " +
            "order by amount_of_play desc " +
            "limit 8")
    public  List<SongInfo> selectSongTop8Info(String type);


    //根据类型查询播放量前8的歌曲userdb@123.57.90.42
    @Select("select * " +
            "from songstable " +
            "order by amount_of_play desc " +
            "limit 8")
    public  List<SongInfo> selectSongTop8_Info();


    //查询最近日期前8的歌曲信息
    @Select("select * " +
            "from songstable " +
            "order by date " +
            "limit 8")
    public List<SongInfo> selectSongTop5Info();

    //根据点赞数找出前10的歌曲
    @Select("select * " +
            "from songstable " +
            "order by like_number desc " +
            "limit 10")
    public List<SongInfo> selectSongTop10_Like_Info();

    //根据收藏量找出前10的歌曲
    @Select("select * " +
            "from songstable " +
            "order by collected desc " +
            "limit 10")
    public  List<SongInfo> selectSongTop10_Collected_Info();

    //根据日期和播放量找前10的歌曲
    @Select("select * " +
            "from songstable " +
            "order by date asc, amount_of_play desc")
    public List<SongInfo> selectSongTop10_NewSongs_Info();
    
    //根据SongInfo的id更新SongInfo信息
    @Update("update songstable " +
            "set song_name=#{s.song_name} , " +
            "song_describing=#{s.song_describing} , " +
            "picture_address=#{s.picture_address} , " +
            "music_address=#{s.music_address} , " +
            "video_address=#{s.video_address} , " +
            "singer_name=#{s.singer_name} , " +
            "date=#{s.date} , " +
            "lyrics=#{s.lyrics} , " +
            "like_number=#{s.like_number} , " +
            "amount_of_play=#{s.amount_of_play} , " +
            "type=#{s.type} , " +
            "collected=#{s.collected} " +
            "where id=#{s.song_id}")
    public void UpdateSongInfo(SongInfo s);

    //根据n返回(n-1)*9开始的9个音乐数据
    @Select("select * " +
            "from songstable " +
            "limit #{page},#{limit}")
    public List<SongInfo> queryPageSong(Integer page,Integer limit);

    @Select("select * from songstable")
    public List<SongInfo> queryAll();

    @Select("select * from songstable " +
            "where lyrics like concat('%',#{lyrics},'%') " +
            "and song_name like concat('%',#{song_name},'%') " +
            "and singer_name like concat('%',#{singer_name},'%')")
    public List<SongInfo> searchBy_id_name_singer(String lyrics,String song_name,String singer_name);

    @Update("delete from songstable" +
            " where song_id = #{song_id}")
    public int deleteById(int song_id);

    //多条件查询2
    @Select("select * from songstable " +
            "where lyrics like concat('%',#{item},'%') " +
            "or song_name like concat('%',#{item},'%') " +
            "or singer_name like concat('%',#{item},'%')")
    List<SongInfo> searchByItem(String item);
    
    //根据song_id修改点赞数
    @Update("update songstable " +
            "set like_number=like_number+#{num} " +
            "where song_id=#{song_id}")
    public int UpdateSongLike(String song_id,Integer num);

    //根据song_id修改收藏数
    @Update("update songstable " +
            "set collected=collected+#{num} " +
            "where song_id=#{song_id}")
    public int UpdateSongCollected(String song_id,Integer num);

    //根据id查找歌曲
    @Select("select * from songstable" +
            " where song_id = #{song_id}")
    public SongInfo selectById(int song_id);
}
